﻿using Anke.SHManage.IDAL;
using Anke.SHManage.Model;
using Anke.SHManage.Utility;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Anke.SHManage.MSSQLDAL
{
    public partial class C_AmbulanceDAL : BaseDAL<C_Ambulance>, IC_AmbulanceDAL
    {


        IDALContext dalContext = new DALContextFactory().GetDALContext();

        AKV7ManageEntities db = DBContextFactory.GetDBContext() as AKV7ManageEntities;


        //public static readonly SqlConnectionStringBuilder ManageBuilder = new SqlConnectionStringBuilder(SqlHelper.MainConnectionString);//取管理库的链接字符串
        //public static readonly string ManageDatabase = ManageBuilder.InitialCatalog;
        /// <summary>
        /// 取最大ambID
        /// </summary>
        /// <returns></returns>
        public int getMaxAmbID()
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(@"
                SELECT  isnull(max ( AmbulanceCode),0)
                FROM C_Ambulance ");



            DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
            int result = 0;
            if (ds.Tables[0].Rows.Count > 0)
            {
                result = Convert.ToInt16(ds.Tables[0].Rows[0][0]);
            }
            return result;

        }

        //获取
        public object getAmbDate(int page, int rows, string sortName, string sortOrder, ref int total, string zcenterCode, string zstationCode, string zPlateNumber, string zCarNumber, DateTime InsuranceDateStart, DateTime InsuranceDateEnd, DateTime AnnualSurveyDateStart, DateTime AnnualSurveyDateEnd, string isActive, string isReject, string messgeIds
            ,E_StatisticsPermisson em, string selfWorkCode, string selfCenterID, string selfStationID)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(@"  select ddr.AmbulanceCode, sum(ddr.DayKM) as CountKM
  into #temp1
  from D_DriverReport ddr
  left join C_Ambulance ca on ddr.AmbulanceCode = ca.AmbulanceCode
  where ddr.ReportDate > ca.MaintainDate
  group by ddr.AmbulanceCode
  select ca.AmbulanceCode,CASE WHEN CountKM >= ca.NextMaintainKM-300  then 'true' else 'false' end as  IsWarn
  into #temp2
  from #temp1 t1
  left join C_Ambulance ca on t1.AmbulanceCode = ca.AmbulanceCode
  group by ca.AmbulanceCode,CountKM,NextMaintainKM;

                        select distinct identity(int,1,1) as 行号,
                               ca.AmbulanceCode
                              ,CenterCode
                             ,tc.名称 as Center
                              ,StationCode, ts.名称 as Station 
                              ,PlateNumber
                              ,CarNumber
                              , tdB.Name  as  Brand 
                              ,Model
                              ,Type 
                              ,UseFor
                              ,EnableDate
                              ,FrameNumber
                              ,EngineNumber
                              ,Price
                              ,AnnualSurveyDate
                              ,AnnualSurveyNumber
                              ,SelectASDateIntervalCode
                              ,AnnualSurveyExpireDate
                              ,AnnualSurveyMoney
                              ,InsuranceDate
                              ,InsuranceNumber
                              ,SelectIDateIntervalCode
                              ,InsuranceExpireDate
                              ,InsuranceMoney
                              ,TotalDriverKM
                              ,SelectAlarmKMCode
                              ,SelectAlarmDateIntervalCode
                              ,SelectColorCode
                              ,Long
                              ,Width
                              ,Height
                              ,Wheelbase
                              ,DriveType
                              ,FrontTread
                              ,BackTread
                              ,SelectTireSizeCode
                              ,Displacement
                              ,Power
                              ,SelectSteeringPositionCode
                              ,TotalKG
                              ,Fuel
                              ,ApprovedPassenger
                              ,ca.isActive
                              ,isRejected
                              ,ca.Remark
                              ,backup1
                              ,backup2
                              ,MaintainDate
                              ,MaintainKM,NextMaintainKM,IsWarn
                    into #temp
                    from  C_Ambulance  ca 
                    left join #temp2 t2 on t2.AmbulanceCode = ca.AmbulanceCode
                ");
            SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(SqlHelper.AttemperConnectionString);
            sb.Append(" left join ").Append(builder.InitialCatalog).Append(".dbo.TCenter tc on ca.CenterCode=tc.编码 ");
            sb.Append(" left join ").Append(builder.InitialCatalog).Append(".dbo.TStation ts on ca.StationCode=ts.编码 ");
            sb.Append(@"
                        left join TDictionary  tdB  on ca.Brand  = tdB.ID
                        left join TDictionary  tdT  on ca.Type  = tdT.ID
                        left join TDictionary  tdU  on ca.UseFor  = tdU.ID
                        left join TDictionary  tdC  on ca.SelectColorCode  = tdC.ID
                        left join TDictionary  tdDT  on ca.DriveType  = tdDT.ID ");

            sb.Append(" where 1=1 ");
            WhereClauseUtility.AddDateTimeGreaterThan("ca.InsuranceExpireDate", InsuranceDateStart, sb);
            WhereClauseUtility.AddDateTimeLessThan("ca.InsuranceExpireDate", InsuranceDateEnd, sb);
            WhereClauseUtility.AddDateTimeGreaterThan("ca.AnnualSurveyExpireDate", AnnualSurveyDateStart, sb);
            WhereClauseUtility.AddDateTimeLessThan("ca.AnnualSurveyExpireDate", AnnualSurveyDateEnd, sb);
           
            WhereClauseUtility.AddInSelectQuery("ca.AmbulanceCode", messgeIds, sb);
            if (isActive != "") { bool isActivez = Convert.ToBoolean(isActive); WhereClauseUtility.AddBoolEqual("ca.IsActive", isActivez, sb); }
            //2016-12-27 zch
            if (isReject != "") { bool isRejectz = Convert.ToBoolean(isReject); WhereClauseUtility.AddBoolEqual("ca.isRejected", isRejectz, sb); }
           
            WhereClauseUtility.AddStringLike("ca.PlateNumber", zPlateNumber, sb);
            WhereClauseUtility.AddStringLike("ca.CarNumber", zCarNumber, sb);


            if (em == E_StatisticsPermisson.None)
                return null;

            else
            {
                if (em == E_StatisticsPermisson.ALL)
                {
                    WhereClauseUtility.AddInSelectQuery("ca.CenterCode", zcenterCode, sb);
                    WhereClauseUtility.AddInSelectQuery("ca.StationCode", zstationCode, sb);



                }
                else if (em == E_StatisticsPermisson.CENTER)
                {
                    WhereClauseUtility.AddStringEqual("ca.CenterCode", selfCenterID, sb);
                    WhereClauseUtility.AddStringEqual("ca.StationCode", zstationCode, sb);

                }
                else if (em == E_StatisticsPermisson.STATION)
                {
                    sb.Append("and 1=2 ");
                    WhereClauseUtility.AddStringEqual("ca.CenterCode", zcenterCode, sb);
                    WhereClauseUtility.AddStringEqual("ca.StationCode", selfStationID, sb);

                }
                else if (em == E_StatisticsPermisson.SELF)
                {
                    WhereClauseUtility.AddStringEqual("ca.CenterCode", selfCenterID, sb);
                    WhereClauseUtility.AddStringEqual("ca.StationCode", selfStationID, sb);


                }
            }

            if (sortName != "AmbulanceCode")
            {
                sb.Append(@"  
                    order by  " + sortName + "  " + sortOrder + " ");
            }
            else
            {
                sb.Append("order by AmbulanceCode  desc");
            } 

        


            sb.Append(@" select top " + rows + " A.*  from #temp A where  行号>" + (page - 1) * rows + "  order by 行号 ");
            sb.Append(@"  SELECT count(*) FROM #temp t ;  drop table #temp; drop table #temp1;
            drop table #temp2 ;");

            List<C_Ambulance> list = new List<C_Ambulance>();
            DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    C_Ambulance info = new C_Ambulance();

                    info.AmbulanceCode = dr["AmbulanceCode"].ToString();
                    info.backup1 = dr["Center"].ToString();
                    info.backup2 = dr["Station"].ToString();
                    info.PlateNumber = dr["PlateNumber"].ToString();
                    info.CarNumber = dr["CarNumber"].ToString();
                    info.Brand = dr["Brand"].ToString();
                    info.EnableDate = Utility.DBConvert.ConvertDBTypeToNullable(dr["EnableDate"]);
                    info.InsuranceExpireDate = Utility.DBConvert.ConvertDBTypeToNullable(dr["InsuranceExpireDate"]);
                    info.AnnualSurveyExpireDate = Utility.DBConvert.ConvertDBTypeToNullable(dr["AnnualSurveyExpireDate"]);
                    info.FrameNumber = dr["FrameNumber"].ToString();

                    info.Remark = dr["Remark"].ToString();
                    info.isActive = Convert.ToBoolean(dr["isActive"]);
                    info.isRejected = Convert.ToBoolean(dr["isRejected"]);
                    if (dr["MaintainDate"] != DBNull.Value)
                    {
                        info.MaintainDate = Convert.ToDateTime(dr["MaintainDate"]);
                    }
                    if (dr["MaintainKM"] != DBNull.Value)
                    {
                        info.MaintainKM = Convert.ToDouble(dr["MaintainKM"]);
                    }
                    if (dr["NextMaintainKM"] != DBNull.Value)
                    {
                        info.NextMaintainKM = Convert.ToDouble(dr["NextMaintainKM"]);
                    }
                    info.IsWarn = dr["IsWarn"].ToString();
                    list.Add(info);
                }
            }
            total = Convert.ToInt32(ds.Tables[1].Rows[0][0]);

            return list;

        }

        //获取车辆基本信息的打印
        public DataTable getAmbDatePrint( string zcenterCode, string zstationCode, string zPlateNumber, string zCarNumber, DateTime InsuranceDateStart, DateTime InsuranceDateEnd,
         DateTime AnnualSurveyDateStart, DateTime AnnualSurveyDateEnd, string isActive, string messgeIds , E_StatisticsPermisson em, string selfWorkCode, string selfCenterID, string selfStationID)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(@"
                        select
                               AmbulanceCode
                              ,CenterCode
                             ,tc.名称 as Center
                              ,StationCode, ts.名称 as Station 
                              ,PlateNumber
                              ,CarNumber
                              , tdB.Name  as  Brand 
                              ,Model
                              ,Type 
                              ,UseFor
                              ,EnableDate
                              ,FrameNumber
                              ,EngineNumber
                              ,Price
                              ,AnnualSurveyDate
                              ,AnnualSurveyNumber
                              ,SelectASDateIntervalCode
                              ,AnnualSurveyExpireDate
                              ,AnnualSurveyMoney
                              ,InsuranceDate
                              ,InsuranceNumber
                              ,SelectIDateIntervalCode
                              ,InsuranceExpireDate
                              ,InsuranceMoney
                              ,TotalDriverKM
                              ,SelectAlarmKMCode
                              ,SelectAlarmDateIntervalCode
                              ,SelectColorCode
                              ,Long
                              ,Width
                              ,Height
                              ,Wheelbase
                              ,DriveType
                              ,FrontTread
                              ,BackTread
                              ,SelectTireSizeCode
                              ,Displacement
                              ,Power
                              ,SelectSteeringPositionCode
                              ,TotalKG
                              ,Fuel
                              ,ApprovedPassenger
                              ,ca.isActive
                              ,isRejected
                              ,ca.Remark
                              ,backup1
                              ,backup2
                
                    from  C_Ambulance  ca
                ");
            SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(SqlHelper.AttemperConnectionString);
            sb.Append(" left join ").Append(builder.InitialCatalog).Append(".dbo.TCenter tc on ca.CenterCode=tc.编码 ");
            sb.Append(" left join ").Append(builder.InitialCatalog).Append(".dbo.TStation ts on ca.StationCode=ts.编码 ");
            sb.Append(@"
                        left join TDictionary  tdB  on ca.Brand  = tdB.ID
                        left join TDictionary  tdT  on ca.Type  = tdT.ID
                        left join TDictionary  tdU  on ca.UseFor  = tdU.ID
                        left join TDictionary  tdC  on ca.SelectColorCode  = tdC.ID
                        left join TDictionary  tdDT  on ca.DriveType  = tdDT.ID ");

            sb.Append(" where 1=1 ");
            WhereClauseUtility.AddDateTimeGreaterThan("ca.InsuranceExpireDate", InsuranceDateStart, sb);
            WhereClauseUtility.AddDateTimeLessThan("ca.InsuranceExpireDate", InsuranceDateEnd, sb);
            WhereClauseUtility.AddDateTimeGreaterThan("ca.AnnualSurveyExpireDate", AnnualSurveyDateStart, sb);
            WhereClauseUtility.AddDateTimeLessThan("ca.AnnualSurveyExpireDate", AnnualSurveyDateEnd, sb);

            WhereClauseUtility.AddInSelectQuery("ca.AmbulanceCode", messgeIds, sb);
            if (isActive != "") { bool isActivez = Convert.ToBoolean(isActive); WhereClauseUtility.AddBoolEqual("ca.IsActive", isActivez, sb); }

            WhereClauseUtility.AddStringLike("ca.PlateNumber", zPlateNumber, sb);
            WhereClauseUtility.AddStringLike("ca.CarNumber", zCarNumber, sb);


            if (em == E_StatisticsPermisson.None)
                return null;

            else
            {
                if (em == E_StatisticsPermisson.ALL)
                {
                    WhereClauseUtility.AddInSelectQuery("ca.CenterCode", zcenterCode, sb);
                    WhereClauseUtility.AddInSelectQuery("ca.StationCode", zstationCode, sb);

                }
                else if (em == E_StatisticsPermisson.CENTER)
                {
                    WhereClauseUtility.AddStringEqual("ca.CenterCode", selfCenterID, sb);
                    WhereClauseUtility.AddStringEqual("ca.StationCode", zstationCode, sb);

                }
                else if (em == E_StatisticsPermisson.STATION)
                {
                    sb.Append("and 1=2 ");
                    WhereClauseUtility.AddStringEqual("ca.CenterCode", zcenterCode, sb);
                    WhereClauseUtility.AddStringEqual("ca.StationCode", selfStationID, sb);

                }
                else if (em == E_StatisticsPermisson.SELF)
                {
                    WhereClauseUtility.AddStringEqual("ca.CenterCode", selfCenterID, sb);
                    WhereClauseUtility.AddStringEqual("ca.StationCode", selfStationID, sb);

                }
            }
                sb.Append("order by AmbulanceCode  desc");
         
            DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
            return ds.Tables[0];

        }




        //获取编辑基础信息
        public void getEditAmb(string ambulanceCode, out C_Ambulance info)
        {
            info = (from a in db.C_Ambulance
                    where (a.AmbulanceCode.Equals(ambulanceCode))
                    select a).FirstOrDefault().ToPOCO();
         
        }

        //  是否存在ID，根据ID看是否存在相同ID 的数据
        public C_AnnualSurvey ExistAS(string tempID)
        {
            var q = (from a in db.C_AnnualSurvey
                     where (a.ID.Equals(tempID))
                     select a).FirstOrDefault();

            return q;
        }

        public bool ifExistAS(string tempID)
        {
            C_AnnualSurvey info = new C_AnnualSurvey();
            info = dalContext.IC_AnnualSurveyDAL.GetModelWithOutTrace(a => a.ID == tempID);

            if (info == null)
            {
                return false;
            }
            else
            {
                return true;
            }

        }

        //是否存在ID，根据ID看是否存在相同ID 的数据
        public C_Insurance ExistI(string tempID)
        {
            var q = (from a in db.C_Insurance
                     where (a.ID.Equals(tempID))
                     select a).FirstOrDefault();
            return q;
        }

        public bool ifExistI(string tempID)
        {
            C_Insurance info = new C_Insurance();
            info = dalContext.IC_InsuranceDAL.GetModelWithOutTrace(a => a.ID == tempID);

            if (info == null)
            {
                return false;
            }
            else
            {
                return true;
            }

        }
        /// <summary>
        /// 根据分站编码获取车牌号和车辆编号
        /// </summary>
        /// <param name="stationCode"></param>
        /// <returns></returns>
        public object getPlateByStationID(string stationCode)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(@"
                    select   
                            AmbulanceCode,PlateNumber
                           ,CarNumber
                            from  C_Ambulance  ca
                            where  1=1");
            WhereClauseUtility.AddInSelectQuery("ca.StationCode", stationCode, sb);

            sb.Append(" and ca.isActive = 1   ");
            sb.Append(" order by  ca.PlateNumber  ");

            List<C_Ambulance> list = new List<C_Ambulance>();
            DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    C_Ambulance info = new C_Ambulance();

                    info.AmbulanceCode = dr["AmbulanceCode"].ToString();

                    info.PlateNumber = dr["PlateNumber"].ToString();
                    info.CarNumber = dr["CarNumber"].ToString();
                    list.Add(info);
                }
            }
            return list;
        }


        /// <summary>
        /// 根据车辆编码获取车辆编号和车牌号码
        /// </summary>
        /// <param name="stationCode"></param>
        /// <returns></returns>
        public object getCarNumberByambID(string ID)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(@"
                    select   
                            AmbulanceCode,PlateNumber
                           ,CarNumber
                            from  C_Ambulance  ca
                            where  1=1");
            WhereClauseUtility.AddInSelectQuery("ca.AmbulanceCode", ID, sb);

            sb.Append(" and ca.isActive = 1 ");

            List<C_Ambulance> list = new List<C_Ambulance>();
            DataSet ds = SqlHelper.ExecuteDataSet(SqlHelper.MainConnectionString, CommandType.Text, sb.ToString(), null);
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    C_Ambulance info = new C_Ambulance();

                    info.AmbulanceCode = dr["AmbulanceCode"].ToString();

                    info.PlateNumber = dr["PlateNumber"].ToString();
                    info.CarNumber = dr["CarNumber"].ToString();
                    list.Add(info);
                }
            }
            return list;
        }

    }
}
